/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ContentManagers.RoleManagers;

import ContentManagers.Models.Attempt;
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

/**
 *
 * @author Martin San Diego
 */
public class AttemptManager {
    public boolean addAttemptRecord(Attempt newAttempt)
    {
        boolean result = false;
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("INSERT into login_attempts (usernameUsed, attemptCount, fromIP) VALUES(?,?,?)");
            pstmt.setString(i++, newAttempt.getUsername());
            pstmt.setInt(i++, newAttempt.getAttemptCount());
            pstmt.setString(i++, newAttempt.getFromIP());
            
            pstmt.executeUpdate();
            
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        
        return result;
    }
    
    public boolean checkIfAlreadyExists(String usernameUsed)
    { 
        boolean result = false;
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM login_attempts where usernameUsed = ?");
            pstmt.setString(i++, usernameUsed);
            //pstmt.setString(i++, fromIP);
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                result = true;
               }
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    }
    
    public boolean addAttemptCount(String username, int attemptCnt, String timestamp, String fromIP)
    {
        boolean result = false;
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();
          
           int i = 1;
            
           
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("UPDATE login_attempts set attemptCount = ?, lastAttempt = ?, fromIP = ? WHERE usernameUsed = ? OR fromIP = ?");
            
            //include parameters
            pstmt.setInt(i++, attemptCnt+1);
            pstmt.setString(i++, timestamp);
            pstmt.setString(i++, fromIP);
            pstmt.setString(i++, username);
            pstmt.setString(i++, fromIP);
            
             //execute query
            pstmt.executeUpdate();
            
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    }
    
    public int latestAttemptCount(String username, String fromIP)
    {   int attemptCnt = 0;
    
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM login_attempts where fromIP = ? ORDER by attemptCount ASC");
            //pstmt.setString(i++, username);
            pstmt.setString(i++, fromIP);
            
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                attemptCnt = rs.getInt("attemptCount");
                System.out.print( rs.getString("usernameUsed") + " - " + attemptCnt);
            }
            conn.close();
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
    
        return attemptCnt;
    }
    
    public boolean clearAttemptCount(String username, String fromIP)
    { boolean result = false;
    
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("admin");
            Connection conn = myFactory.getConnection();
          
           int i = 1;
            
           
            //SQL Query
            PreparedStatement pstmt = conn.prepareStatement("DELETE from login_attempts WHERE usernameUsed = ?");
            
            //include parameters
            //pstmt.setInt(i++, 0);
            pstmt.setString(i++, username);
            //pstmt.setString(i++, fromIP);
            
             //execute query
            pstmt.executeUpdate();
            
            conn.close();
            
            //check muna kung may iciclear :)
           result = true;
            
       }  catch (SQLException ex) {
            ex.printStackTrace();
        }
     return result;
    }
    
    public boolean checkTimeStamp()
    {   
        java.util.Date date= new java.util.Date();
        Timestamp currTime = new Timestamp(date.getTime()-100000); //make this 300000 = 5 minutes
        boolean cleared = false;
        
        
        try {
            ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
            Connection conn = myFactory.getConnection();
          
            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM login_attempts");
            //System.out.print("im here to clear");
            
            ResultSet rs = pstmt.executeQuery();
            //System.out.print("im here to clear");
                
            while (rs.next()) {
                //System.out.print(rs.getTimestamp("lastAttempt") + " - " + (currTime));
                if(rs.getTimestamp("lastAttempt").before(currTime))
                {   
                    cleared = true;
                    clearAttemptCount(rs.getString("usernameUsed"), rs.getString("fromIP"));
                }              
            }
            conn.close();
            
            }  catch (SQLException ex) {
                ex.printStackTrace();
        }
        
        return cleared;
    }
    
    public static ResultSet getUsernamesToLock(String remoteIP)
    {   ResultSet usernames = null;
                
        try {
                ConnectionFactory myFactory = ConnectionFactory.getFactory("customer");
                Connection conn = myFactory.getConnection();

                int i = 1;


                //SQL Query
                PreparedStatement pstmt = conn.prepareStatement("UPDATE login_attempts set attemptCount = ?, lastAttempt = ?, fromIP = ? WHERE usernameUsed = ? OR fromIP = ?");

                //include parameters
                pstmt.setString(i++, remoteIP);


                conn.close();

                
            
            }  catch (SQLException ex) {
                ex.printStackTrace();
            }
        
        return usernames;
    }
}
